﻿using System.Linq;
using System.Text;
using System.Diagnostics;
using System.Runtime.Serialization;
using System.Collections;
using System.Data;
using System.ComponentModel;
using System.Data.OracleClient;
namespace INTEX.DataBase.Tools
{
    public partial class INTEXDBC
    {
        public void MapICC_EVLCOST(ref DataRow dwLocal, DataRow dwData)
        {
            BlindMap(ref dwLocal, dwData);
        }
        public void SaveICC_EVLCOST(DataRow dwData, string ITXDBVERSION = "50.24")
        {
            DataSet Result = new DataSet();
            DataTable tbLocal = null;
            DataRow dwLocal = null;
            bool flgExist = false;
            Result = QueryICC_EVLCOST(dwData);
            if (Result.Tables.Count > 0)
            {
                tbLocal = Result.Tables[0];
                if (tbLocal.Rows.Count > 0)
                {
                    flgExist = true;
                    dwLocal = tbLocal.Rows[0];
                }
                else
                {
                    dwLocal = tbLocal.NewRow();
                }
            }

            MapICC_EVLCOST(ref dwLocal, dwData);

            if (!flgExist)
            {
                InsertICC_EVLCOST(dwData, 1);
            }
            UpdateICC_EVLCOST(dwLocal);

        }
        public void InsertICC_EVLCOST(DataRow dwData, int option = 1)
        {

            or1 = OracleDB.CreateCommand();
            switch (option)
            {

                case 1://20140501
                    or1.CommandText = "INSERT INTO ICC_EVLCOST"
                    + " (ROWNID,MANDANT,MONTH,LO,FART,FVAR,FARTDES,MART,MVAR,MARTDES,MARTCOM,PMART,TR00,VTR00,TR99,VTR99,DSTOCK,DMOVE,DMOVSTOCK, TR10,TR11,VTR10, AB36,TAB36,PTAB36,DPRICE,AB13,TAB13)"
                    + " SELECT SEQ_ICC_ITX.NEXTVAL AS ROWNID, X.MANDANT,X.MONTH,X.LO,NVL(A.FART,' '),A.FVAR,NVL(A.FARTDES,' '),  X.MART,X.MVAR,X.MARTDES,X.MARTCOM,  "
                    + "  X.PMART,X.TR00,X.VTR00,X.TR99,VTR99,DSTOCK,DMOVE, X.DMOVSTOCK,  TR10,TR11,VTR10,nvl(AB36,0),nvl(A.TAB36,0),nvl(A.PTAB36,0),DPRICE,AB13,TAB13  "
                    + " FROM ICC_EXSTOCK X RIGHT JOIN ICC_ABCOST A ON A.MANDANT=X.MANDANT AND A.LO=X.LO AND A.MONTH=X.MONTH AND A.MART=X.MART AND X.MVAR=X.MVAR "
                    + " WHERE X.MANDANT=:MANDANT: AND X.MONTH=:MONTH:";
                    or1.CommandText = InjectData(or1.CommandText, dwData);
                    break;
                default:
                    break;
            }
            Connect2Proxy(or1);
        }
        public void DeleteICC_EVLCOST(DataRow dwData, int option = 1)
        {

            or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1:
                    or1.CommandText = "DELETE FROM ICC_EVLCOST X WHERE X.MANDANT=:MANDANT:";
                    or1.CommandText = InjectData(or1.CommandText, dwData);
                    break;
                case 2:
                    or1.CommandText = "DELETE FROM ICC_EVLCOST X WHERE X.MANDANT=:MANDANT: AND X.MONTH=:MONTH:";
                    or1.CommandText = InjectData(or1.CommandText, dwData);
                    break;
                default:
                    break;
            }
            Connect2Proxy(or1);
        }

        public DataSet QueryICC_EVLCOST(DataRow dwData, int option = 1)
        {
            DataSet SResult = new DataSet();
            or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1:
                    or1.CommandText = "SELECT * FROM ICC_EVLCOST  X WHERE X.MANDANT=:MANDANT: AND X.MONTH=:MONTH:";
                    or1.CommandText = InjectData(or1.CommandText, dwData);
                    break;
                case 2:
                    or1.CommandText = "SELECT * FROM ICC_EVLCOST  X "
                    + " WHERE X.MANDANT=:MANDANT: AND X.MONTH=:MONTH: AND LO LIKE '" + dwData["LO"].ToString().Trim() + "%' AND"
                    + " MART LIKE '" + dwData["MART"].ToString().Trim() + "%' AND"
                    + " FART LIKE '" + dwData["FART"].ToString().Trim() + "%'"
                    + " ORDER BY MANDANT, MONTH, LO, FART,MART,MFARBE";
                    or1.CommandText = InjectData(or1.CommandText, dwData);
                    break;
                default:
                    break;
            }
            SResult = Connect2Proxy(or1);
            SResult.Tables[0].TableName = "ICC_EVLCOST";

            return SResult;
        }
        public void UpdateICC_EVLCOST(DataRow dwData, int option = 1)
        {
            or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1:
                    #region====Command====
                    or1.CommandText = "UPDATE ICC_EVLCOST SET "
 + "COMPANY=:COMPANY,"
+ "USERID=:USERID,"
+ "PROCESS=:PROCESS,"
+ "MENUITEM=:MENUITEM,"
+ "MENUID=:MENUID,"
+ "FATHER=:FATHER,"
+ "HEADER=:HEADER,"
+ "TAG=:TAG,"
+ "ICON=:ICON,"
+ "LARGEICON=:LARGEICON,"
+ "JOB=:JOB,"
+ "TIMER=:TIMER,"
+ "SCHEDULETYPE=:SCHEDULETYPE,"
+ "FREQUENCY=:FREQUENCY,"
+ "TIME=:TIME "
+ " WHERE  "
+ "  MANDANT=:MANDANT AND ROWNID=:ROWNID";
                    #endregion

                    #region====Parameters====
                    or1.Parameters.Add("MANDANT", OracleType.NVarChar).Value = dwData["MANDANT"].ToString();
                    or1.Parameters.Add("ROWNID", OracleType.NVarChar).Value = dwData["ROWNID"].ToString();
                    or1.Parameters.Add("COMPANY", OracleType.NVarChar).Value = dwData["COMPANY"].ToString();
                    or1.Parameters.Add("USERID", OracleType.NVarChar).Value = dwData["USERID"].ToString();
                    or1.Parameters.Add("PROCESS", OracleType.NVarChar).Value = dwData["PROCESS"].ToString();
                    or1.Parameters.Add("MENUITEM", OracleType.NVarChar).Value = dwData["MENUITEM"].ToString();
                    or1.Parameters.Add("MENUID", OracleType.NVarChar).Value = dwData["MENUID"].ToString();
                    or1.Parameters.Add("FATHER", OracleType.NVarChar).Value = dwData["FATHER"].ToString();
                    or1.Parameters.Add("HEADER", OracleType.NVarChar).Value = dwData["HEADER"].ToString();
                    or1.Parameters.Add("TAG", OracleType.NVarChar).Value = dwData["TAG"].ToString();
                    or1.Parameters.Add("ICON", OracleType.NVarChar).Value = dwData["ICON"].ToString();
                    or1.Parameters.Add("LARGEICON", OracleType.NVarChar).Value = dwData["LARGEICON"].ToString();
                    or1.Parameters.Add("JOB", OracleType.NVarChar).Value = dwData["JOB"].ToString();
                    or1.Parameters.Add("TIMER", OracleType.NVarChar).Value = dwData["TIMER"].ToString();
                    or1.Parameters.Add("SCHEDULETYPE", OracleType.NVarChar).Value = dwData["SCHEDULETYPE"].ToString();
                    or1.Parameters.Add("FREQUENCY", OracleType.NVarChar).Value = dwData["FREQUENCY"].ToString();
                    or1.Parameters.Add("TIME", OracleType.NVarChar).Value = dwData["TIME"].ToString();
                    #endregion
                    break;
                case 2:
                    or1.CommandText = "UPDATE ICC_EVLCOST X SET STOCKCOST=ROUND(DSTOCK*PTAB36,2) WHERE X.MANDANT=:MANDANT: AND X.MONTH=:MONTH:";
                    or1.CommandText = InjectData(or1.CommandText, dwData);
                    break;
                case 3:
                    or1.CommandText = "UPDATE ICC_EVLCOST X SET VSTOCKCOST=ROUND(STOCKCOST*DPRICE,2),WASTE=ROUND(STOCKCOST-AB36,2),VTR00=ROUND(TR00*DPRICE,2),VTR99=ROUND(TR99*DPRICE,2),VTR10=ROUND(TR10*DPRICE,2) WHERE X.MANDANT=:MANDANT: AND X.MONTH=:MONTH:";
                    or1.CommandText = InjectData(or1.CommandText, dwData);
                    break;
                case 4:
                    or1.CommandText = "UPDATE  ICC_EVLCOST C SET (TR00AUF)=(SELECT QTYKG FROM  ICC_ZUTEIGP WHERE QTYPE='QT00' AND C.FART=ABPART AND FVAR=ABPVAR AND C.MART=ZUART AND MVAR=ZUVAR AND LO=ZULO AND MONTH=:MONTH: AND ICCDAT=:SETDATE:) WHERE    EXISTS ( SELECT 1 FROM  ICC_ZUTEIGP WHERE QTYPE='QT00' AND  C.FART=ABPART AND FVAR=ABPVAR AND C.MART=ZUART AND MVAR=ZUVAR AND LO=ZULO AND MONTH=:MONTH: AND ICCDAT=:SETDATE:)";
                    or1.CommandText = InjectData(or1.CommandText, dwData);
                    break;
                case 5:
                    or1.CommandText = "UPDATE  ICC_EVLCOST C SET (TR99AUF)=(SELECT QTYKG FROM  ICC_ZUTEIGP WHERE QTYPE='QT99' AND C.FART=ABPART AND FVAR=ABPVAR AND C.MART=ZUART AND MVAR=ZUVAR AND LO=ZULO AND MONTH=:MONTH: AND ICCDAT=:SETDATE:) WHERE    EXISTS ( SELECT 1 FROM  ICC_ZUTEIGP WHERE QTYPE='QT99' AND C.FART=ABPART AND FVAR=ABPVAR AND C.MART=ZUART AND MVAR=ZUVAR AND LO=ZULO AND MONTH=:MONTH: AND ICCDAT=:SETDATE:)";
                    or1.CommandText = InjectData(or1.CommandText, dwData);
                    break;
                case 6:
                    or1.CommandText = "UPDATE  ICC_EVLCOST C SET (TR10AUF)=(SELECT QTYKG FROM  ICC_ZUTEIGP WHERE QTYPE='QT10' AND C.FART=ABPART AND FVAR=ABPVAR AND C.MART=ZUART AND MVAR=ZUVAR AND LO=ZULO AND MONTH=:MONTH: AND ICCDAT=:MONTH:) WHERE    EXISTS ( SELECT 1 FROM  ICC_ZUTEIGP WHERE QTYPE='QT10' AND C.FART=ABPART AND FVAR=ABPVAR AND C.MART=ZUART AND MVAR=ZUVAR AND LO=ZULO AND MONTH=:MONTH: AND ICCDAT=:MONTH:)";
                    or1.CommandText = InjectData(or1.CommandText, dwData);
                    break;
                case 7:
                    or1.CommandText = "UPDATE  ICC_EVLCOST C SET (TR11AUF)=(SELECT QTYKG FROM  ICC_ZUTEIGP WHERE QTYPE='QT11' AND C.FART=ABPART AND FVAR=ABPVAR AND C.MART=ZUART AND MVAR=ZUVAR AND LO=ZULO AND MONTH=:MONTH: AND ICCDAT=:MONTH:) WHERE    EXISTS ( SELECT 1 FROM  ICC_ZUTEIGP WHERE QTYPE='QT11' AND C.FART=ABPART AND FVAR=ABPVAR AND C.MART=ZUART AND MVAR=ZUVAR AND LO=ZULO AND MONTH=:MONTH: AND ICCDAT=:MONTH:)";
                    or1.CommandText = InjectData(or1.CommandText, dwData);
                    break;
            }
            Connect2Proxy(or1);
        }
    }
}
